package com.dldata.devtools30.utils.jdbc;

import com.dldata.devtools30.utils.DBTypeUtil;
import com.dldata.devtools30.utils.DateUtil;
import com.dldata.devtools30.utils.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jakarta.persistence.Column;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

/**
 * jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
 * jdbc.url=jdbc:sqlserver://127.0.0.1:1433;databaseName=MPEHR_YSSJ
 * jdbc.username=sa
 * jdbc.password=123456
 * Created by 呵呵 on 2017-11-28.
 */
public class JdbcDataAcessObj {

    private final static Logger logger = LoggerFactory.getLogger(JdbcDataAcessObj.class);

    private String driver;

    private String username;

    private String url;

    private String password;

    private static JdbcDataAcessObj jdbcDataAcess;

    public synchronized static JdbcDataAcessObj getJdbcDataAcess(Properties properties) {
        if (jdbcDataAcess == null) {
            jdbcDataAcess =
                    createJdbcDataAcess(properties.getProperty("jdbc.driver"),
                            properties.getProperty("jdbc.url"),
                            properties.getProperty("jdbc.username"),
                            properties.getProperty("jdbc.password"));
        }
        return jdbcDataAcess;
    }

    public static JdbcDataAcessObj createJdbcDataAcess(String driver, String url, String username, String password) {
        JdbcDataAcessObj jdbcDataAcess = new JdbcDataAcessObj();
        jdbcDataAcess.driver = driver;
        jdbcDataAcess.username = username;
        jdbcDataAcess.url = url;
        jdbcDataAcess.password = password;

        Connection connection = null;
        try {
            Class.forName(driver);
            connection = jdbcDataAcess.getConnection();
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            jdbcDataAcess.closeConnection(connection, null, null);
        }
        return jdbcDataAcess;
    }

    public List<String> paraseSqlHeader(String sql, List<Object> parameters) throws SQLException {
        DBTypeUtil.DBTYPE dbtype = DBTypeUtil.getDbtype(url);
        int skipNum = 0;
        if (DBTypeUtil.DBTYPE.mysql.equals(dbtype)) {
            sql = sql.replaceAll(";", "") + " limit " + 0 + " , " + 1;
        } else if (DBTypeUtil.DBTYPE.oracle.equals(dbtype)) {
            StringBuilder tmpStringBuilder = new StringBuilder();
            tmpStringBuilder.append(" select * from ");
            tmpStringBuilder.append(" ( ");
            tmpStringBuilder.append(sql.replaceAll(";", "").trim().replace("select ", "select rownum as rownum_id,"));
            tmpStringBuilder.append(" ) ");
            tmpStringBuilder.append(" where rownum_id between " + 1 + " and " + 2);
            sql = tmpStringBuilder.toString();
        } else if (DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)) {
            StringBuilder tmpStringBuilder = new StringBuilder();
            tmpStringBuilder.append(" select top ");
            tmpStringBuilder.append(1);
            tmpStringBuilder.append(" ");
            sql = sql.replace("select", tmpStringBuilder.toString());
        } else {
            //TODO:其它数据库
            throw new SQLException("数据库不支持.");
        }


        List<String> headerLable = new LinkedList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object parameter = parameters.get(i);
                preparedStatement.setObject(i + 1, parameter);
            }
            logger.info("executeQuery: " + sql);
            logger.info("parameters: " + StringUtil.arrayToDelimitedString(parameters.toArray(), ","));
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                headerLable.add(resultSetMetaData.getColumnLabel(i + 1));
            }
        } catch (Exception ex) {
            logger.error(ex.getMessage(), ex);
            throw ex;
        } finally {
            closeConnection(connection, resultSet, preparedStatement);
        }
        return headerLable;
    }

    public void closeConnectionAndCommit(Connection connection, ResultSet resultSet, Statement preparedStatement, boolean commit) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            if (commit) {
                try {
                    connection.commit();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else {
                try {
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void closeConnection(Connection connection, ResultSet resultSet, Statement preparedStatement) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                if (!connection.getAutoCommit())
                    connection.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() throws SQLException {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException ex) {
            logger.error(ex.getMessage(), ex);
            throw ex;
        }
    }

    public List<HashMap> dbsearch(String sql) {
        return dbsearch(sql, new LinkedList<>());
    }

    public List<HashMap> dbsearch(String sql, List<Object> parameters) {
        List<HashMap> relust = new LinkedList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object parameter = parameters.get(i);
                preparedStatement.setObject(i + 1, parameter);
            }
            logger.info("executeQuery: " + sql);
            logger.info("parameters: " + StringUtil.arrayToDelimitedString(parameters.toArray(), ","));
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            while (resultSet.next()) {
                HashMap<String, Object> stringObjectHashMap = new HashMap<>();
                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                    String columnName = resultSetMetaData.getColumnLabel(i);
                    switch (resultSetMetaData.getColumnType(i)) {
                        case Types.DATE: {
                            Date anInt = resultSet.getDate(i);
                            String dateString = anInt == null ? "" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, anInt);
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TIME: {
                            Time time = resultSet.getTime(i);
                            String dateString = time == null ? "" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(time.getTime()));
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TIMESTAMP: {
                            Object timestamp1 = resultSet.getObject(i);
                            Timestamp timestamp = new Timestamp(0);
                            timestamp = resultSet.getTimestamp(i);
                            String dateString = timestamp == null ? "" : DateUtil.formatFromDate(DateUtil.FORMATER_YYYY_MM_DD_HH_MM_SS, new java.util.Date(timestamp.getTime()));
                            stringObjectHashMap.put(columnName, dateString);
                            break;
                        }
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER: {
                            ;
                            long anInt = resultSet.getLong(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.FLOAT: {
                            ;
                            float anInt = resultSet.getFloat(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE: {
                            ;
                            double anInt = resultSet.getDouble(i);
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                        case Types.CLOB: {
                            ;
                            try {
                                Clob colb = (Clob) resultSet.getObject(i);
                                stringObjectHashMap.put(columnName, colb == null ? "" : colb.getSubString(1, (int) colb.length()));
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        case Types.BLOB: {
                            ;
                            try {
                                Blob blob = (Blob) resultSet.getObject(i);
//                            stringObjectHashMap.put(columnName,new String(blob.getBytes(0, (int) blob.length()),"UTF-8"));
                                //@TODO 后面考虑转二进制可显示串
                                stringObjectHashMap.put(columnName, blob == null ? "" : blob.toString());
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                            break;
                        }
                        default: {
                            String anInt = resultSet.getString(i);
                            anInt = anInt == null ? "" : anInt;
                            try {
//                            anInt=URLEncoder.encode(anInt,"UTF-8");
                                //支持1.0数据有编码
//                            anInt= URLDecoder.decode(anInt, "UTF-8");
//                            anInt= EscapeUnescape.escape(anInt);
//                            anInt =  StringUtil.escape(anInt);
                            } catch (Exception e) {
                            }
                            stringObjectHashMap.put(columnName, anInt);
                            break;
                        }
                    }
                }
                relust.add(stringObjectHashMap);
            }


        } catch (Exception ex) {
            logger.error(ex.getMessage(), ex);
        } finally {
            closeConnection(connection, null, null);
        }
        return relust;
    }

    public int dbUpdate(String sql) {
        return dbUpdate(sql, new LinkedList<>());
    }

    public int dbUpdate(String sql, List<Object> parameters) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < parameters.size(); i++) {
                Object parameter = parameters.get(i);
                preparedStatement.setObject(i + 1, parameter);
            }
            logger.info("dbUpdate: " + sql);
            logger.info("parameters: " + StringUtil.arrayToDelimitedString(parameters.toArray(), ","));
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            logger.error(ex.getMessage(), ex);
            return -1;
        } finally {
            closeConnection(connection, null, null);
        }
    }

    public String getDriver() {
        return driver;
    }

    public String getUsername() {
        return username;
    }

    public String getUrl() {
        return url;
    }

    public String getPassword() {
        return password;
    }

    public void save(Object entity) {
        if (isExists(entity)) {
            update(entity);
            return;
        }
        Object[] sqlAndParam = getInsertSqlAnd(entity);
        List<Object> parameters = (List<Object>) sqlAndParam[1];
        dbUpdate(sqlAndParam[0].toString(), parameters);
    }

    public void update(Object entity) {
        Object[] sqlAndParam = getUpdateSql(entity);
        List<Object> parameters = (List<Object>) sqlAndParam[1];
        dbUpdate(sqlAndParam[0].toString(), parameters);
    }

    public static Object[] getInsertSqlAnd(Object entity) {
        Table table = entity.getClass().getAnnotation(Table.class);
        String sql = "insert into {0} ({1}) values ({2})";
        Method[] methods = entity.getClass().getMethods();
        LinkedList parameters = new LinkedList();
        String fields = "";
        String values = "";
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];
            Column annotation = method.getAnnotation(Column.class);
            if (method.getName().startsWith("get") && annotation != null) {
                fields = fields + annotation.name() + ",";
                values = values + "?,";
                try {
                    parameters.add(method.invoke(entity, new Object[0]));
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        if (fields.length() > 0) {
            fields = fields.substring(0, fields.length() - 1);
            values = values.substring(0, values.length() - 1);
        }
        Object[] result = new Object[2];
        result[0] = MessageFormat.format(sql, table.name(), fields, values);
        result[1] = parameters;
        return result;
    }

    public Object[] getUpdateSql(Object entity) {
        Table table = entity.getClass().getAnnotation(Table.class);
        String sql = "update {0} set {1} where {2}=? ";
        Method[] methods = entity.getClass().getMethods();
        LinkedList parameters = new LinkedList();
        String fields = "";
        String idFieldName = "";
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];
            Column annotationColumn = method.getAnnotation(Column.class);
            Id annotationId = method.getAnnotation(Id.class);
            Object value = null;
            if (method.getName().startsWith("get") && annotationColumn != null) {
                if (annotationId == null) {
                    fields = fields + annotationColumn.name() + "=?,";
                }
                try {
                    value = method.invoke(entity, new Object[0]);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
                if (annotationId != null) {
                    idFieldName = annotationColumn.name();
                    parameters.add(value);
                } else if (idFieldName == null) {
                    parameters.add(value);
                } else {
                    parameters.add(parameters.size() - 1, value);
                }
            }

        }
        if (fields.length() > 0) {
            fields = fields.substring(0, fields.length() - 1);
        }
        Object[] result = new Object[2];
        result[0] = MessageFormat.format(sql, table.name(), fields, idFieldName);
        result[1] = parameters;
        return result;
    }

    public Boolean isExists(Object entity) {
        Table table = entity.getClass().getAnnotation(Table.class);
        String sql = "select count(1) as total from {0} where {1}=? ";
        Method[] methods = entity.getClass().getMethods();
        LinkedList parameters = new LinkedList();
        String idFieldName = "";
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];
            Column annotationColumn = method.getAnnotation(Column.class);
            Id annotationId = method.getAnnotation(Id.class);
            if (annotationId != null) {
                idFieldName = annotationColumn.name();
                try {
                    parameters.add(method.invoke(entity, new Object[0]));
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        String sqlExec = MessageFormat.format(sql, table.name(), idFieldName);
        List dbsearch = dbsearch(sqlExec, parameters);
        if (dbsearch.size() == 0) {
            return false;
        }
        return Integer.parseInt(((HashMap) dbsearch.get(0)).get("total").toString()) > 0;
    }
}
